package pers.nickerg.conver;

import com.mysql.jdbc.StringUtils;
import org.apache.commons.io.FileUtils;
import pers.nickerg.bean.ColumnBean;
import pers.nickerg.bean.TableBean;
import pers.nickerg.config.Constant;
import pers.nickerg.util.OracleUtil;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

/**
 * 将Oracle的表迁移到Mysql
 *
 * @author PF0P65Z6
 */
public class UpdateTableToMysql {
    public static void main(String args[]) throws Exception {
        long start = System.currentTimeMillis();

        int count = 0;
        String fileName = "mysql.sql";
        File file = new File(Constant.FILE_PATH + fileName);
        if (file.exists()) {
            file.delete();
        }
        file.createNewFile();
        List<TableBean> tableList = OracleUtil.getAllTableName();
        List<String> sqlLineList = new ArrayList<>();
        for (TableBean tableBean : tableList) {
            String createSql = createTable(tableBean.getTableName(), tableBean.getTableComment());
            if(!StringUtils.isNullOrEmpty(createSql)){
                sqlLineList.add(createSql);
            }
            count++;
        }
        //    	createTable("TEST","test");
        //写入文件中
        FileUtils.writeLines(file, sqlLineList);

        long end = System.currentTimeMillis();
        System.out.println("oracle表结构迁移到mysql已完成，总共涉及表" + count + "张,用时" + (end - start) / 1000 + "秒");
    }

    /**
     * 将Oracle的表复制到MySQL中
     */
    public static String createTable(String tableName, String tableComment) {
        Connection conn = null;
        PreparedStatement ps = null;

        String createTableSql = getMysqlCreateSql(tableName, tableComment);
        System.out.println(createTableSql);

        // try {
        // 	conn = MysqlUtil.getConnection();
        //     ps = conn.prepareStatement(createTableSql);
        //     ps.execute();
        // } catch (SQLException e) {
        //     e.printStackTrace();
        // }finally{
        // 	MysqlUtil.releaseDB(ps);
        // }
        return createTableSql;
    }

    /**
     * 把oracle创建表语句改为mysql创建表语句
     */
    private static String getMysqlCreateSql(String tableName, String tableComment) {
        List<ColumnBean> colList = OracleUtil.getColList(tableName);
        StringBuffer sb = new StringBuffer();

        for (int i = 0; i < colList.size(); i++) {
            ColumnBean col = colList.get(i);
            col.setColumnTypeMs(getMySQLType(tableName, col));
            //设置默认
            //mysql BLOB, TEXT, GEOMETRY or JSON column  can't have a default value
            if(!col.getColumnTypeMs().toUpperCase().contains("BLOB")
                    &&!col.getColumnTypeMs().toUpperCase().contains("TEXT")
                    &&!col.getColumnTypeMs().toUpperCase().contains("GEOMETRY")
                    &&!col.getColumnTypeMs().toUpperCase().contains("JSON")) {
                //主键不能有默认值
                if (StringUtils.isNullOrEmpty(col.getPrimaryKey())) {
                    if (!StringUtils.isNullOrEmpty(col.getDefaultValue()) && !col.getDefaultValue().equals("null")) {
                        sb.append("alert TABLE ").append(tableName.toLowerCase());
                        sb.append(" alert ").append(col.getColumnName().toLowerCase());
                        sb.append(" set default " + col.getDefaultValue() + ";\n");
                    }
                }
            }
        }

        return sb.toString();
    }

    private static boolean checkDataLength(String primaryDataLength) {
        if(StringUtils.isNullOrEmpty(primaryDataLength)){
            return true;
        }
        String[] split = primaryDataLength.split(",");
        for (String s : split) {
            if(s.equals("null")){
                continue;
            }
            if(Long.valueOf(s)>100){
                return false;
            }
        }
        return true;
    }

    /**
     * 把Oracle的Type转成Mysql的Type
     */
    private static String getMySQLType(String tableName, ColumnBean c) {
        if (c == null) {
            return null;
        }

        int dataLength = c.getDataLength();
        int dataPrecision = c.getDataPrecision();
        int dataScale = c.getDataScale();
        String type = c.getColumnType();

        if ("BLOB".equalsIgnoreCase(type) || "CLOB".equalsIgnoreCase(type) || "RAW".equalsIgnoreCase(type)) {
            return "longtext";
        } else if ("DATE".equalsIgnoreCase(type)) {
            return "datetime";
        } else if ("CHAR".equalsIgnoreCase(type)) {
            return "char";
        } else if ("VARCHAR2".equalsIgnoreCase(type)) {
            if (c.getDataLength() >= 1000) {
                return "text";
            } else {
                return "varchar(" + dataLength + ")";
            }
        } else if ("NUMBER".equalsIgnoreCase(type)) {
            if (dataScale > 0) {
                return "decimal(" + dataPrecision + "," + dataScale + ")";
            } else {
                if (dataPrecision == 0) {
                    dataPrecision = dataLength;
                }

                if (dataPrecision <= 3) {
                    return "tinyint(" + dataPrecision + ")";
                } else if (dataPrecision <= 5) {
                    return "smallint(" + dataPrecision + ")";
                } else if (dataPrecision <= 7) {
                    return "mediumint(" + dataPrecision + ")";
                } else if (dataPrecision <= 10) {
                    return "int(" + dataPrecision + ")";
                } else {
                    return "bigint(" + dataPrecision + ")";
                }
            }
        } else if ("NVARCHAR2".equalsIgnoreCase(type)) {
            return "nvarchar(" + dataLength + ")";
            // } else if ("TIMESTAMP".equalsIgnoreCase(type)) {
            //     if (dataLength>0) {
            //         return "datetime(" + dataLength + ")";
            //     }
            //     return type.replace("TIMESTAMP", "datetime");
        } else if (type.contains("TIMESTAMP")) {
            return "datetime";
        } else if ("FLOAT".equalsIgnoreCase(type)) {
            return "double";
        }

        System.out.println("WARN:表" + tableName + ",字段" + c.getColumnName() + "，转换mysql，使用默认类型" + c.getColumnType());
        return c.getColumnType();
    }
}